package mongo;

import com.mongodb.BasicDBObject;
import com.mongodb.DBObject;
import com.mongodb.client.FindIterable;
import com.mongodb.client.MongoCursor;
import com.mongodb.client.model.Filters;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.bson.Document;
import org.bson.types.ObjectId;

import com.mongodb.MongoClient;
import com.mongodb.client.MongoCollection;
import com.mongodb.client.MongoDatabase;

public class CourseClass {
	
    MongoDatabase mongoDatabase = null;
    MongoCollection<Document> CourseClass;
    
    public CourseClass(){
        try{
        	mongoDatabase = mongoConnet.getMongoDatabase();
        	CourseClass = mongoDatabase.getCollection("CourseClass");
        }catch(Exception e){
            System.err.println( e.getClass().getName() + ": " + e.getMessage() );
        }
    }
    
    public  MongoCursor<Document> listTeacherCourseClass(String teachercode){
        try{
        	BasicDBObject queryObject = new BasicDBObject("teachercode",teachercode);
            FindIterable<Document> findIterable = CourseClass.find(queryObject);
            MongoCursor<Document> mongoCursor = findIterable.iterator();
            return mongoCursor;
        }catch(Exception e){
            System.err.println( e.getClass().getName() + ": " + e.getMessage() );
            return null;
        }
    }
    
    public  ArrayList<Document> listByTeachercode(String teachercode){
        try{
        	ArrayList<Document> result = new ArrayList<Document>();
        	BasicDBObject queryObject = new BasicDBObject("teachercode",teachercode);
            FindIterable<Document> findIterable = CourseClass.find(queryObject);
            MongoCursor<Document> mongoCursor = findIterable.iterator();
            while(mongoCursor.hasNext()){
            	Document document = mongoCursor.next();
            	result.add(document);
            }
            return result;
        }catch(Exception e){
            System.err.println( e.getClass().getName() + ": " + e.getMessage() );
            return null;
        }
    }
    
    public  MongoCursor<Document> managerlist(){
        try{
            FindIterable<Document> findIterable = CourseClass.find();
            MongoCursor<Document> mongoCursor = findIterable.iterator();
            return mongoCursor;
        }catch(Exception e){
            System.err.println( e.getClass().getName() + ": " + e.getMessage() );
            return null;
        }
    }
    
    public  MongoCursor<Document> query(BasicDBObject query){
        try{
            FindIterable<Document> findIterable = CourseClass.find(query);
            MongoCursor<Document> mongoCursor = findIterable.iterator();
            return mongoCursor;
        }catch(Exception e){
            System.err.println( e.getClass().getName() + ": " + e.getMessage() );
            return null;
        }
    }
    
    public  Document queryOne(ObjectId courseclassid){
        try{
        	BasicDBObject queryObject = new BasicDBObject("_id",courseclassid);
            FindIterable<Document> findIterable = CourseClass.find(queryObject);
            MongoCursor<Document> mongoCursor = findIterable.iterator();
            if(mongoCursor.hasNext()){
            	return mongoCursor.next();
            }else{
            	return null;
            }
        }catch(Exception e){
            System.err.println( e.getClass().getName() + ": " + e.getMessage() );
            return null;
        }
    }
    
    
    public Boolean add(Document courseclass){
    	try{
    		CourseClass.insertOne(courseclass);
    		return true;
    	}catch(Exception e){
            System.err.println( e.getClass().getName() + ": " + e.getMessage() );
            return false;
    	}
    }
    
    
    public boolean update(ObjectId id,Document document){
        try{
        	CourseClass.updateOne(Filters.eq("_id", id), new Document("$set",document));
            return true;
        }catch(Exception e){
            System.err.println( e.getClass().getName() + ": " + e.getMessage() );
            return false;
        }
    }
    
    public boolean delete(ObjectId id){
        try{
            BasicDBObject queryObject = new BasicDBObject("_id",id);
            CourseClass.deleteOne(queryObject);
            return true;
        }catch(Exception e){
            System.err.println( e.getClass().getName() + ": " + e.getMessage() );
            return false;
        }
    }
    
    public Boolean isExist(String coursecode){
        try{
            BasicDBObject queryObject = new BasicDBObject("coursecode",coursecode);
            FindIterable<Document> findIterable = CourseClass.find(queryObject);
            MongoCursor<Document> mongoCursor = findIterable.iterator();
            if(mongoCursor.hasNext()){
            	return true;
            }else{
            	return false;
            } 
        }catch(Exception e){
            System.err.println( e.getClass().getName() + ": " + e.getMessage() );
            return false;
        }
    }
    
    public List<String> batchAdd(String fileDir,Document yearSemester){
    	try{
    		List<String> result = new ArrayList<String>();
    		List<Document> courseclasslist =  new ArrayList<Document>();
    		String prefix=fileDir.substring(fileDir.lastIndexOf(".")+1);
    		System.out.println(prefix);
    		if(prefix.equals("xls")){
    			courseclasslist = this.readByXls(fileDir);
    		}else if(prefix.equals("xlsx")){
    			courseclasslist = this.readByXlsx(fileDir);
    		}else{
    			return null;
    		}
    		for(int i=0;i<courseclasslist.size();i++){
    			courseclasslist.get(i).append("year", yearSemester.getString("year"));
    			courseclasslist.get(i).append("semester", yearSemester.getString("semester"));
    			if(this.isExist(courseclasslist.get(i).getString("coursecode"))){
    				result.add(courseclasslist.get(i).getString("coursecode"));
    			}else{
    				this.add(courseclasslist.get(i));
    			}
    		}
    		System.out.println("batchAddSuccess");
    		return result;
    	}catch(Exception e){
            System.err.println( e.getClass().getName() + ": " + e.getMessage() );
            return null;
    	}
    }
    
    private List<Document> readByXls(String fileDir) throws IOException{
    	List<Document> result = new ArrayList<Document>();
    	try {    		
			InputStream is = new FileInputStream(fileDir);
			HSSFWorkbook hssfworkbook = new HSSFWorkbook(is);
			for(int numSheet = 0 ; numSheet < hssfworkbook.getNumberOfSheets();numSheet++){
				HSSFSheet hssfSheet = hssfworkbook.getSheetAt(numSheet);
				if(hssfSheet == null){
					continue;
				}

				HSSFRow firstRow = hssfSheet.getRow(0);
				int allElements = firstRow.getLastCellNum();
				for(int rowNum = 1; rowNum <= hssfSheet.getLastRowNum();rowNum++){
					HSSFRow hssfRow = hssfSheet.getRow(rowNum);
					Document courseclass = new Document();
					for(int i=0;i<allElements;i++){
						if(hssfRow.getCell(i) !=null){
							String cellValue = "";
		                    switch (hssfRow.getCell(i).getCellType()) {  
		                    case HSSFCell.CELL_TYPE_NUMERIC: // 数字  
		                        DecimalFormat df = new DecimalFormat("0");  
		                        cellValue = df.format(hssfRow.getCell(i).getNumericCellValue());  
		                        break;  
		  
		                    case HSSFCell.CELL_TYPE_STRING: // 字符串  
		                        cellValue = hssfRow.getCell(i).getStringCellValue();  
		                        break;  
		  
		                    case HSSFCell.CELL_TYPE_ERROR: // 故障  
		                        cellValue = "非法字符";  
		                        break;  
		  
		                    default:  
		                        cellValue = "未知类型";  
		                        break;  
		                    }  
		                    courseclass.append(firstRow.getCell(i).toString(), cellValue);
						}else{
							courseclass.append(firstRow.getCell(i).toString(), "无");
						}
					}
					result.add(courseclass);
					}
				
			}
			return result;
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	return result;
    }
	
    
    private List<Document> readByXlsx(String fileDir) throws IOException{
    	List<Document> result = new ArrayList<Document>();
    	try {    		
			InputStream is = new FileInputStream(fileDir);
			XSSFWorkbook xssfworkbook = new XSSFWorkbook(is);
			for(int numSheet = 0 ; numSheet < xssfworkbook.getNumberOfSheets();numSheet++){
				XSSFSheet xssfSheet = xssfworkbook.getSheetAt(numSheet);
				if(xssfSheet == null){
					continue;
				}

				XSSFRow firstRow = xssfSheet.getRow(0);
				int allElements = firstRow.getLastCellNum();
				for(int rowNum = 1; rowNum <= xssfSheet.getLastRowNum();rowNum++){
					XSSFRow hssfRow = xssfSheet.getRow(rowNum);
					Document courseclass = new Document();
					for(int i=0;i<allElements;i++){
						if(hssfRow.getCell(i) !=null){
							String cellValue = "";
		                    switch (hssfRow.getCell(i).getCellType()) {  
		                    case HSSFCell.CELL_TYPE_NUMERIC: // 数字  
		                        DecimalFormat df = new DecimalFormat("0");  
		                        cellValue = df.format(hssfRow.getCell(i).getNumericCellValue());  
		                        break;  
		  
		                    case HSSFCell.CELL_TYPE_STRING: // 字符串  
		                        cellValue = hssfRow.getCell(i).getStringCellValue();  
		                        break;  
		  
		                    case HSSFCell.CELL_TYPE_ERROR: // 故障  
		                        cellValue = "非法字符";  
		                        break;  
		  
		                    default:  
		                        cellValue = "未知类型";  
		                        break;  
		                    }  
		                    courseclass.append(firstRow.getCell(i).toString(), cellValue);
						}else{
							courseclass.append(firstRow.getCell(i).toString(), "无");
						}
					}
					result.add(courseclass);
					}
				
			}
			return result;
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
	    	return result;
		}
    }

}
